package com.gongj;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;

public class ExcelReadTest {

  private static final String PATH = "D:\\gongj\\excel-demo\\poi-excel\\";


  @Test
  public void testRead03() throws IOException {
      FileInputStream is = new FileInputStream(PATH + "testWrite03.xls");
      // 创建工作簿
      Workbook workbook = new HSSFWorkbook(is);
      //获得指定Sheet页
      Sheet sheet = workbook.getSheetAt(0);
      // 读取第一行第一列
      Row row = sheet.getRow(0);
      Cell cell = row.getCell(0);
      // 输出单元格内容
      System.out.println(cell.getStringCellValue());
      // 关闭流
      is.close();
  }

    @Test
    public void testRead07() throws IOException {
        FileInputStream is = new FileInputStream(PATH + "testWrite07.xlsx");
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook(is);
        //获得指定Sheet页
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第二行第一列
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(0);
        // 输出单元格内容
        System.out.println(cell.getNumericCellValue());
        // 关闭流
        is.close();
    }

    @Test
    public void testsCellType() throws IOException {
        FileInputStream is = new FileInputStream(PATH + "模板.xls");
        Workbook workbook = new HSSFWorkbook(is);

        Sheet sheet = workbook.getSheetAt(0);
        // 读取标题行
        Row titleRow = sheet.getRow(0);
        if(titleRow != null){
            // 获取一行里不为空的列个数
            int cellCount = titleRow.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                // 获得每个列对象
                Cell cell = titleRow.getCell(cellNum);
                System.out.print(cell.getStringCellValue() + " | ");
            }
        }
        //获得所有的行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        System.out.println("rowCount = " + rowCount);
        // 从1开始 排除标题行
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if(row != null){
                //获取一行里不为空的列个数
                int cellCount = titleRow.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    // 获得每个列对象
                    Cell cell = row.getCell(cellNum);
                    if(cell != null){
                        String cellValue = "";
                        // 获得每个列对象的类型
                        int cellType = cell.getCellType();
                        switch (cellType){
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("数值类型：【NUMERIC】");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    // 日期
                                    System.out.print("【数值转换成日期】");
                                    Date date = cell.getDateCellValue();
                                    SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
                                    cellValue = sdf.format(date);
                                }else{
                                    System.out.print("数值类型：【NUMERIC】");
                                // 不是日期格式，则防止当数字过长时以科学计数法显示
                                    System.out.print("【数值转换成字符串】");
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case XSSFCell.CELL_TYPE_STRING:
                                System.out.print("字符串类型：【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_BLANK:
                                System.out.print("空类型：【BLANK】");
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.print("布尔类型：【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case XSSFCell.CELL_TYPE_ERROR:
                                System.out.print("错误类型：【ERROR】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 关闭流
        is.close();
    }

    @Test
    public void testFormula() throws Exception {
        InputStream is = new FileInputStream(PATH + "公式.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第三行第一列
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(0);
        //公式计算器
        FormulaEvaluator formulaEvaluator = new
                HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA://2
                //得到公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }

}
